package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import model.Recipe;
import model.RecipeIngredient;
import model.RecipeStep;


/**
 * @author Natalie
 *
 */
public class RecipeDatabase implements DatabaseM3 {

	private Connection conn;
	private Statement statement;

	/**
	 * Constructor for the RecipeDatabase
	 */
	public RecipeDatabase() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://xtremeduty.com:3306/recipes?user=recipes&password=recipes");
			statement = conn.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#validateUser(java.lang.String, java.lang.String)
	 */
	@Override
	public Boolean validateUser(String userID, String password) {
		String query = "SELECT * FROM User WHERE User.User_ID = '" + userID + "';";
		ResultSet result;
		try {
			result = statement.executeQuery(query);
			if (!result.isBeforeFirst() ) {    //no user with the userID passed in 
				return false;
			}
			result.absolute(1);
			if(result.getString("Password").equals(password)) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#searchRecipes(java.util.List)
	 */
	@Override
	public List<Recipe> searchRecipes(List<String> conditions) {
		String attribute;
		String value;
		String sSEARCHSTRING = "";
		String sCooking_Time = "";
		String sPrep_Time = "";
		String sCuisine = "";
		String sCourse = "";
		String sDifficulty = "";
		String sIngredient = "";
		String query = "SELECT Recipe_ID, Title, author, Description, Notes, Difficulty, Serving_Size, Prep_Time, Cooking_Time, Picture" +
				" FROM recipes.Recipes WHERE ";
		for(int i = 0; i < conditions.size(); i+= 2) {
			attribute = conditions.get(i);
			value = conditions.get(i + 1);
			if(attribute.equals("SEARCHSTRING")) {
				String[] stringSplit = value.split("\\s+");
				for(int j = 0; j < stringSplit.length; j++) {
					if(j != stringSplit.length - 1) {
						sSEARCHSTRING += "Title LIKE '%" + stringSplit[j] + "%' OR ";
					} else {
						sSEARCHSTRING += "Title LIKE '%" + stringSplit[j] + "%'";
					}
				}	
			} else if (attribute.equals("Cooking_Time")) {
				sCooking_Time += attribute + " < " + value;
			} else if (attribute.equals("Prep_Time")) {
				sPrep_Time += attribute + " < " + value;
			} else if(attribute.equals("Course")) {
				if(sCourse.equals("")) {
					sCourse += attribute + " = '" + value + "'";
				} else {
					sCourse += " OR " + attribute + " = '" + value + "'";
				}
			} else if(attribute.equals("Cuisine")) {
				if(sCuisine.equals("")) {
					sCuisine += attribute + " = '" + value + "'";
				} else {
					sCuisine += " OR " + attribute + " = '" + value + "'";
				}
			} else if(attribute.equals("Difficulty")) {
				if(sDifficulty.equals("")) {
					sDifficulty += attribute + " = '" + value + "'";
				} else {
					sDifficulty += " OR " + attribute + " = '" + value + "'";
				}
			} else if(attribute.equals("Ingredient")) {
				if(sIngredient.equals("")) {
					sIngredient += "Recipes.Recipe_ID in " +
							"(SELECT Recipe_ID FROM Recipe_Ingredients WHERE Ingredient_ID in " +
							"(SELECT Ingredient_ID FROM Ingredients WHERE Description like '%" + value + "%'))";
				} else {
					sIngredient += " AND Recipes.Recipe_ID in (SELECT Recipe_ID FROM Recipe_Ingredients WHERE Ingredient_ID in " +
                            "(SELECT Ingredient_ID FROM Ingredients WHERE Description like '%" + value + "%' ))";
				}
			}
		}
		//sIngredient += "))";
		ArrayList<String> querySegments = new ArrayList<String>();
		querySegments.add(sSEARCHSTRING);
		querySegments.add(sCooking_Time);
		querySegments.add(sPrep_Time);
		querySegments.add(sCourse);
		querySegments.add(sCuisine);
		querySegments.add(sDifficulty);
		querySegments.add(sIngredient);
		for (int i = 0; i < querySegments.size(); i ++) {
			if(querySegments.get(i).equals("") || querySegments.get(i).equals("))")) {
				querySegments.remove(i);
				i--;
			}
		}
		for(int i = 0; i < querySegments.size(); i++) {
			if(i != querySegments.size() - 1) {
				query += "(" + querySegments.get(i) + ") AND ";
			} else {
				query += "(" + querySegments.get(i) + ");";
			}
		}
		return queryDatabase(query);
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#favoriteRecipes(java.lang.String)
	 */
	@Override
	public List<Recipe> getFavoriteRecipes(String UserID) {
		return queryDatabase(queryRecipeString("recipes.Favorites", "Favorites.User_ID", UserID));
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#recentlyViewedRecipes(java.lang.String)
	 */
	@Override
	public List<Recipe> getRecentlyViewedRecipes(String userID) {
		//return in order newest to oldest
		String query = "SELECT Recipes.Recipe_ID, Title, author, Description, Notes, Difficulty, Serving_Size, Prep_Time, Cooking_Time, Picture, Recently_Viewed.Timestamp " +
				"FROM recipes.Recipes, Recently_Viewed WHERE Recently_Viewed.Recipe_ID = Recipes.Recipe_ID " +
				"AND Recently_Viewed.User_ID = '" + userID + "' ORDER BY Timestamp DESC;";
		return queryDatabase(query);
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#recipeInformation(int)
	 */
	@Override
	public List<String> getRecipeInformation(int RecipeID) {
		String recipeID = "" + RecipeID;
		return querySingle("Recipes", "Recipes.Recipe_ID", recipeID);
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#userInformation(java.lang.String)
	 */
	@Override
	public List<String> getUserInformation(String UserID) {
		return querySingle("User", "User.User_ID", UserID);
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#addUser(java.util.List)
	 */
	@Override
	public Boolean addUser(List<String> userInformation) {
		String insert = "INSERT INTO recipes.User (User_ID, Name, Password) VALUES ('" +
						userInformation.get(0) + "', '" + userInformation.get(1) + "', '" + userInformation.get(2) + "');";
		return executeUpdate(insert);
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#addRecipe(java.util.List)
	 */
	@Override
	public Boolean addRecipe(List<String> newRecipe, List<List<String>> ingredients, List<String> steps) {
		try {
			//Insert Recipe Data
			String insert = "INSERT INTO recipes.Recipes ";
			ResultSet result = statement.executeQuery("SELECT MAX(Recipe_ID) as Recipe_ID from Recipes");
			result.absolute(1);
			int id = Integer.parseInt(result.getString("Recipe_ID")) + 1;
			String attributes = "(Recipe_ID";
			String values = "('" + id + "'";
			for(int i = 0; i < newRecipe.size(); i+=2) {
				attributes += ", " + newRecipe.get(i) + "";
				values += ", '" + newRecipe.get(i + 1) + "'";
			}
			insert += attributes + ") VALUES " + values + ");";
			statement.executeUpdate(insert);
			//Insert Ingredient Data
			if(!addIngredients(id, ingredients)) {
				return false;
			}
			//Insert Step Data
			if(!addSteps(id, steps)) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	/* (non-Javadoc)
	 * @see database.DatabaseM3#addFavorite(java.lang.String, model.Recipe)
	 */
	@Override
	public Boolean addFavorite(String userID, Recipe favorite) {
		String insert = "INSERT INTO recipes.Favorites (User_ID, Recipe_ID) VALUES ('" + userID + "', " + favorite.getID() + ");";
		return executeUpdate(insert);
	}
	
	/* (non-Javadoc)
	 * @see database.DatabaseM3#addRecentView(java.lang.String, java.lang.String, java.sql.Timestamp)
	 */
	@Override
	public Boolean addRecentView(String userID, String recipeID, Timestamp currentTime) {
		ResultSet result;
		String insert = "";
		try {
			result = statement.executeQuery("SELECT * FROM Recently_Viewed WHERE User_ID = '" +
								userID + "' AND Recipe_ID = '" + recipeID + "';");
			if(!result.isBeforeFirst() ) {
				insert += "INSERT INTO recipes.Recently_Viewed (User_ID, Recipe_ID, Timestamp) " +
						"VALUES ('" + userID + "', '" + recipeID + "', '" + currentTime + "');";
			} else {
				insert += "UPDATE recipes.Recently_Viewed SET Timestamp='" + 
						currentTime + "' WHERE User_ID='" + userID + "' AND Recipe_ID='" + recipeID + "';";

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}				
		return executeUpdate(insert);
	}
	
	/* (non-Javadoc)
	 * @see database.DatabaseM3#addRecipeView(int)
	 */
	@Override
	public Boolean addRecipeView(int recipeID) {
		ResultSet result;
		String insert = "";
		try {
			result = statement.executeQuery("SELECT Number_of_Views FROM Recipes WHERE Recipe_ID = '" +
								recipeID + "';");
			if(!result.isBeforeFirst() ) {
				return false;
			} else {
				result.absolute(1);
				String viewCount = result.getString("Number_of_Views");
				int views = 1;
				if(viewCount != null) {
					views = Integer.parseInt(viewCount) + 1;
				}
				insert += "UPDATE recipes.Recipes SET Number_of_Views='" + 
						views + "' WHERE Recipe_ID='" + recipeID + "';";

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}				
		return executeUpdate(insert);
	}
	
	
	/* (non-Javadoc)
	 * @see database.DatabaseM3#editRecipe(java.util.List)
	 */
	@Override
	public Recipe editRecipe(int recipeID, List<String> recipeInformation, List<List<String>> ingredients, List<String> steps) {
		//Update recipe information
		String update = "UPDATE recipes.Recipes SET ";
		for(int i = 0; i < recipeInformation.size() - 1; i+=2) {
			update += recipeInformation.get(i) + " = ";
			if(i != recipeInformation.size() - 2) {
				update += "'" + recipeInformation.get(i + 1) + "' , ";
			} else {
				update += "'" + recipeInformation.get(i + 1) + "' ";
			}
		}
		update += "WHERE Recipe_ID = '" + recipeID + "';";
		try {
			statement.executeUpdate(update);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//Replace Ingredients
		executeUpdate("DELETE FROM recipes.Recipe_Ingredients WHERE Recipe_ID='"+ recipeID + "';");
		addIngredients(recipeID,ingredients);
		//Replace Steps
		executeUpdate("DELETE FROM recipes.Recipe_Steps WHERE Recipe_ID='"+ recipeID + "';");
		addSteps(recipeID, steps);
		
		//Create and return new recipe object
		String query = ("SELECT Recipe_ID, Title, author, Description, Notes, Difficulty, Serving_Size, Prep_Time, Cooking_Time, Picture" +
				" FROM recipes.Recipes WHERE Recipes.Recipe_ID = '" + recipeID + "';");
		List<Recipe> result = queryDatabase(query);
		return result.get(0);
	}


	/* (non-Javadoc)
	 * @see database.DatabaseM3#getStaticValues(java.lang.String)
	 */
	@Override
	public List<String> getStaticValues(String type, String attribute) {
		List<String> values = new ArrayList<String>();
		ResultSet result;
		try {
			result = statement.executeQuery("SELECT " + attribute + " FROM " + type + ";");
			ResultSetMetaData meta;
			meta = result.getMetaData();
			int colCnt = meta.getColumnCount();
			while(result.next()) {
				for (int i = 1; i <= colCnt; i++) {
					values.add(result.getString(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return values;
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#removeFavorite(java.lang.String, int)
	 */
	@Override
	public void removeFavorite(String userID, int recipeID) {
		String delete = "DELETE FROM recipes.Favorites WHERE User_ID='" + userID + 
				"' AND Recipe_ID = '" + recipeID + "';";
		executeUpdate(delete);
	}

	/* (non-Javadoc)
	 * @see database.DatabaseM3#isFavorite(int, java.lang.String)
	 */
	@Override
	public Boolean isFavorite(int recipeID, String userID) {
		String query = "SELECT * FROM Favorites WHERE User_ID = '" + userID + "' AND Recipe_ID = '" + recipeID + "';";
		ResultSet result;
		try {
			result = statement.executeQuery(query);
			if (result.isBeforeFirst() ) {    //no user with the userID passed in 
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	private Boolean addSteps(int recipeID, List<String> steps) {
		String insert;
		for(int i = 0; i < steps.size(); i++) {
			insert = "INSERT INTO recipes.Recipe_Steps (Recipe_ID, Step_Number, Directions) VALUES ("
					+ recipeID + ", " + i + ", '" + steps.get(i) + "');";
			try {
				statement.executeUpdate(insert);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}	
		}
		return true;
	}
	
	
	private Boolean addIngredients(int recipeID, List<List<String>> ingredients) {
		//List<List<String>> = {{Description,Style_Prep, Unit_Measure, quantity},...}
		//Search for description- if exists, use ID - if not exist, create new with ID
		String unit, id;
		ResultSet result;

		for(int i = 0; i < ingredients.size(); i++) {
			try {
				result = statement.executeQuery("SELECT Ingredient_ID FROM Ingredients WHERE Description like '" + ingredients.get(i) + "';");
				if (result.isBeforeFirst() ) {   
					result.absolute(1);
					id = result.getString("Ingredient_ID");
					
				} else{  //no ingredient with this description, add one
					result = statement.executeQuery("SELECT MAX(Ingredient_ID) as id FROM Ingredients;");
					result.absolute(1);
					int maxID = Integer.parseInt(result.getString("id")) + 1 ;
					id = "" + maxID;
					statement.executeUpdate("INSERT INTO recipes.Ingredients (Ingredient_ID, Description) VALUES ('" + id + "', '" + ingredients.get(i).get(0) + "');");
				}
				//Get Unit Measure ID
				result = statement.executeQuery("SELECT Unit_Measure_ID FROM Unit_Measures WHERE Description like '" + ingredients.get(i).get(2) + "';");
				if (result.isBeforeFirst() ) {    //no ingredient with this description 
					result.absolute(1);
					unit = result.getString("Unit_Measure_ID");
				} else {
					unit = "0";
				}
				//Quantity
				String quantityResult = ingredients.get(i).get(3);
				if (quantityResult == null | quantityResult.isEmpty()) {
				    quantityResult = "0.0";
				}
				statement.executeUpdate("INSERT INTO `recipes`.`Recipe_Ingredients` (`Recipe_ID`, `Ingredient_ID`, `Quantity`, `Style_Prep`, `Unit_Measure_ID`) VALUES " +
						"('" + recipeID + "', '" + id + "', '" + quantityResult + "', '" + ingredients.get(i).get(1) + "', '" + unit +"');");
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return true;
	}

	private ArrayList<Recipe> queryDatabase(String query) {
		ArrayList<Recipe> recipes = new ArrayList<Recipe>();
		ArrayList<String> recipeAttributes = new ArrayList<String>();
		ArrayList<String> recipeInfo = new ArrayList<String>();
		ResultSet result = null;
		try {
			result = statement.executeQuery(query);
			ResultSetMetaData meta;
			meta = result.getMetaData();
			int colCnt = meta.getColumnCount();
			for (int i = 1; i <= colCnt; i++) {
				recipeAttributes.add(meta.getColumnName(i));
			}
			while(result.next()) {
				for (int i = 1; i <= colCnt; i++) {
					recipeInfo.add(result.getString(i));
				}
				Integer servingSize = null;
				Integer prepTime = null;
				Integer cookTime = null;
				if(recipeInfo.get(6) != null) {
					servingSize = Integer.parseInt(recipeInfo.get(6));
				}
				if(recipeInfo.get(7) != null) {
					prepTime = Integer.parseInt(recipeInfo.get(7));
				}
				if(recipeInfo.get(8) != null) {
					cookTime = Integer.parseInt(recipeInfo.get(8));

				}
				int recipeID = Integer.parseInt(recipeInfo.get(0));

				recipes.add(new Recipe(recipeID, recipeInfo.get(1), recipeInfo.get(2), recipeInfo.get(3), recipeInfo.get(4), recipeInfo.get(5), 
						servingSize, prepTime, cookTime, recipeInfo.get(9)));
				recipeInfo = new ArrayList<String>();
			}

			for(int k = 0; k < recipes.size(); k++) {
				int recipeID = recipes.get(k).getID();
				List<RecipeIngredient> ingredients = new ArrayList<RecipeIngredient>();
				List<String> ingInfo = new ArrayList<String>();
				ResultSet ingredientResult = statement.executeQuery("SELECT Quantity, Style_Prep, Abbreviation, Ingredients.Description " +
						"FROM Ingredients, (SELECT Ingredient_ID AS ing, Quantity, Style_Prep, Abbreviation " +
						"FROM Recipe_Ingredients left join Unit_Measures ON Recipe_Ingredients.Unit_Measure_ID = Unit_Measures.Unit_Measure_ID " +
						"WHERE Recipe_ID = "+ recipeID +") AS RJ WHERE Ingredients.Ingredient_ID = ing;");
				meta = ingredientResult.getMetaData();
				int cCnt = meta.getColumnCount();
				while(ingredientResult.next()) {
					ingInfo = new ArrayList<String>();
					for (int i = 1; i <= cCnt; i++) {
						ingInfo.add(ingredientResult.getString(i));
					}

					Double quantity = null;
					if(ingInfo.get(0) != null) {
						quantity = Double.parseDouble(ingInfo.get(0));
					}
					ingredients.add(new RecipeIngredient(quantity, ingInfo.get(1), ingInfo.get(2), ingInfo.get(3)));
				}
				recipes.get(k).addIngredients(ingredients);
				
				List<RecipeStep> steps = new ArrayList<RecipeStep>();
				List<String> stepInfo = new ArrayList<String>();
				ResultSet stepResult = statement.executeQuery("SELECT Step_Number, Directions " +
						"FROM Recipe_Steps WHERE Recipe_ID = " + recipeID +";");
				meta = stepResult.getMetaData();
				int ccCnt = meta.getColumnCount();
				while(stepResult.next()) {
					stepInfo = new ArrayList<String>();
					for (int i = 1; i <= ccCnt; i++) {
						stepInfo.add(stepResult.getString(i));
					}
					steps.add(new RecipeStep(Integer.parseInt(stepInfo.get(0)), stepInfo.get(1)));
				}
				recipes.get(k).addSteps(steps);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return recipes;

	}

	private String queryRecipeString(String table, String attribute, String UserID){
		return "SELECT Recipe_ID, Title, author, Description, Notes, Difficulty, Serving_Size, Prep_Time, Cooking_Time, Picture" +
				" FROM recipes.Recipes WHERE Recipes.Recipe_ID in " +
				"(SELECT Recipe_ID FROM " + table + " WHERE " + attribute + " = '" + UserID + "');";
	}

	private ArrayList<String> querySingle(String table, String attribute, String ID) {
		ArrayList<String> recipeInfo = new ArrayList<String>();
		ResultSet result = null;
		try {
			result = statement.executeQuery("SELECT * FROM " + table + " WHERE " + attribute + " = " + ID + ";");
			ResultSetMetaData meta;
			meta = result.getMetaData();
			int colCnt = meta.getColumnCount();
			for (int i = 1; i <= colCnt; i++) {
				recipeInfo.add(meta.getColumnName(i));
			}
			while(result.next()) {
				for (int i = 1; i <= colCnt; i++) {
					recipeInfo.add((i * 2) - 1, result.getString(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return recipeInfo;
	}

	private Boolean executeUpdate(String update) {
		try {
			statement.executeUpdate(update);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
